<?php

  //connect to database
  @connectToDb($dbServer, $dbReadOnly, $dbReadOnlyPW, $dbName);

  function getProductDetails($description, $dep, $brand, $size, $colour, $price1, $price2)
  {
    $sql = "SELECT prod_no, prod_brand, prod_size, prod_colour, prod_description, prod_price FROM product WHERE prod_description LIKE '%" . $description . "%' AND prod_dep_no = " . $dep;

    if($brand != "" || !empty($brand))
    {
      $sql = $sql . " AND prod_brand = '" . $brand . "'";
    }
    if($size != "" || !empty($size))
    {
      $sql = $sql . " AND prod_size = '" . $size . "'";
    }
    if($colour != "" || !empty($colour))
    {
      $sql = $sql . " AND prod_colour = '" . $colour . "'";
    }
    if($price1 != "" || !empty($price1) || $price2 != "" || !empty($price2))
    {
      $sql = $sql . " AND prod_price = " . $price1 . "." . $price2;
    }

    $sql = $sql . " ORDER BY prod_no";
	
    $sql_result = @mysql_query($sql);

    $sql_num = @mysql_numrows($sql_result);

    if($sql_num == 0)
    {
      return "0";
    }
    else
    {
      $i = 0;
      while($row = mysql_fetch_array($sql_result))
      {
        $products[$i] = $row["prod_no"];
		$i++;
        $products[$i] = $row["prod_brand"];
		$i++;
        $products[$i] = $row["prod_size"];
		$i++;
        $products[$i] = $row["prod_colour"];
		$i++;
        $products[$i] = $row["prod_description"];
		$i++;
        $products[$i] = $row["prod_price"];
		$i++;
      }

      return $products;
    }

  }

  function getRelatedProductDetails($description, $dep, $brand, $size, $colour, $price1, $price2)
  {
    $sql = "SELECT prod_no, prod_brand, prod_size, prod_colour, prod_description, prod_price FROM product as p WHERE (prod_dep_no = " . $dep . " OR prod_description LIKE '%" . $description . "%'";

	$matchingSQL = "SELECT prod_no, prod_brand, prod_size, prod_colour, prod_description, prod_price FROM product WHERE prod_description LIKE '%" . $description . "%' AND prod_dep_no = " . $dep;

    if($brand != "" || !empty($brand))
    {
      $sql = $sql . " OR prod_brand = '" . $brand . "'";
      $matchingSQL = $matchingSQL . " AND prod_brand = '" . $brand . "'";
    }
    if($size != "" || !empty($size))
    {
      $sql = $sql . " OR prod_size = '" . $size . "'";
      $matchingSQL = $matchingSQL . " AND prod_size = '" . $size . "'";
    }
    if($colour != "" || !empty($colour))
    {
      $sql = $sql . " OR prod_colour = '" . $colour . "'";
      $matchingSQL = $matchingSQL . " AND prod_colour = '" . $colour . "'";
    }
    if($price1 != "" || !empty($price1) || $price2 != "" || !empty($price2))
    {
      $sql = $sql . " OR prod_price = " . $price1 . "." . $price2;
      $matchingSQL = $matchingSQL . " AND prod_price = " . $price1 . "." . $price2;
    }

	$sql = $sql . ") AND NOT EXISTS (" . $matchingSQL . " AND p.prod_no = prod_no) ORDER BY prod_no";

	$sql_result = @mysql_query($sql);

    $sql_num = @mysql_numrows($sql_result);

    if($sql_num == 0)
    {
      return "0";
    }
    else
    {
      $i = 0;
      while($row = mysql_fetch_array($sql_result))
      {
        $products[$i] = $row["prod_no"];
		$i++;
        $products[$i] = $row["prod_brand"];
		$i++;
        $products[$i] = $row["prod_size"];
		$i++;
        $products[$i] = $row["prod_colour"];
		$i++;
        $products[$i] = $row["prod_description"];
		$i++;
        $products[$i] = $row["prod_price"];
		$i++;
      }

      return $products;
    }
  }

  function getStockLevel($prod_no)
  {
    $sql = "SELECT COUNT(*) FROM item WHERE prod_no = " . $prod_no . " AND item_status = 'A'";

    $sql_result = @mysql_query($sql);

    $row = @mysql_fetch_array($sql_result);

	return $row[0];

  }

  function getDepNo($dep)
  {
    $sql = "SELECT dep_no FROM department WHERE dep_name = '" . $dep . "'";

    $sql_result = @mysql_query($sql);

    $row = @mysql_fetch_array($sql_result);

	return $row[0];

  }

  function getDepName($prod_no)
  {
    $sql = "SELECT dep_name FROM product, department WHERE prod_no = " . $prod_no . " AND prod_dep_no = dep_no";

    $sql_result = @mysql_query($sql);

    $row = @mysql_fetch_array($sql_result);

	return $row[0];

  }

  function getAllProductDetails()
  {
    $sql = "SELECT prod_no, prod_colour, prod_size, prod_description, prod_price FROM product ORDER BY prod_no";
	
    $sql_result = @mysql_query($sql);

    $sql_num = @mysql_numrows($sql_result);

    if($sql_num == 0)
    {
      return "0";
    }
    else
    {
      $i = 0;
      $j = 0;
      while($row = mysql_fetch_array($sql_result))
      {
        $product[$i] = $row["prod_no"];
		$i++;
        $product[$i] = $row["prod_colour"];
		$i++;
        $product[$i] = $row["prod_size"];
		$i++;
        $product[$i] = $row["prod_description"];
		$i++;
        $product[$i] = $row["prod_price"];
		$i = 0;
		$allProducts[$j] = $product;
		$j++;
      }

      return $allProducts;
    }

  }

  function getDepartments()
  {
    $sql = "SELECT dep_name FROM department WHERE NOT dep_name = 'Mail Order' AND NOT dep_name = 'Telesales' ORDER BY dep_name";

    $sql_result = @mysql_query($sql);

    $sql_num = @mysql_numrows($sql_result);

    if($sql_num == 0)
    {
      return "0";
    }

    $i = 0;

    while($row = mysql_fetch_array($sql_result))
    {
      $dep[$i] = $row[0];
      $i++;
    }
    return $dep;
  }

  function getStockLevels()
  {
    $sql = "SELECT prod_no, count(*) FROM item WHERE item_status = 'A' GROUP BY prod_no ORDER BY prod_no";
	
    $sql_result = @mysql_query($sql);

    $sql_num = @mysql_numrows($sql_result);

    if($sql_num == 0)
    {
      return "0";
    }
    else
    {
      $i = 0;
      while($row = mysql_fetch_array($sql_result))
      {
		$stockLevel[$i][0] = $row[0];
        $stockLevel[$i][1] = $row[1];
        $i++;
      }

      return $stockLevel;
    }

  }

?>